﻿using Model;
using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
using Utility.Helper;

namespace BLL
{
    public class Qs_SequencesBLL : BaseBLL
    {
        public Qs_Sequences Find(string tablename, string colname)
        {
            var sql = @"SELECT
	                        T1.SEQUENCE_NAME AS C_TABLE_NAME,
	                        '' AS C_COL_NAME,
	                        T1.MIN_VALUE AS N_START_NUMBER,
	                        T1.LAST_NUMBER AS N_CURRENT_NUMBER,
	                        (T1.LAST_NUMBER +1)  AS N_NEXT_NUMBER,
	                        1 AS N_IDENTITY_VALUE
                        FROM ALL_SEQUENCES T1
                        WHERE
	                        T1.SEQUENCE_OWNER = 'THKQS' AND
                            T1.SEQUENCE_NAME  = :SEQUENCE_NAME";
            var parms = ParmsHelper.GetParms();
            parms.Add(":SEQUENCE_NAME", tablename.ToUpper());
            sql = string.Format(sql, Transference(tablename.ToUpper()), Transference(colname.ToUpper()));
            var dt = Query(sql, parms);
            if (dt.Rows.Count == 0)
            {
                return null;
            }
            return ConvertToEntity(dt)[0];
        }

        public int INSERT(Qs_Sequences model)
        {
            string sql = @"CREATE SEQUENCE {0}
                               INCREMENT BY 1
                               START WITH {1}
                               NOMAXVALUE
                               NOMINVALUE
                               NOCYCLE
                               CACHE 20";
            sql = string.Format(sql, model.CurC_Table_Name.ToUpper(), model.CurN_Current_Number);
            NoQuery(sql);
            return 1;
        }

        public int UPDATE(Qs_Sequences model)
        {
            Delete(model.CurC_Table_Name, "");
            INSERT(model);
            return 1;
        }

        public bool Delete(string table, string col)
        {
            var sql = "DROP SEQUENCE {0}";
            sql = string.Format(sql, table);
            NoQuery(sql);

            return true;
        }

        public int GetRecordCount(string whereStr)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(@"SELECT count(1) FROM (SELECT
	                        T1.SEQUENCE_NAME AS C_TABLE_NAME,
	                        '' AS C_COL_NAME,
	                        T1.MIN_VALUE AS N_START_NUMBER,
	                        T1.LAST_NUMBER AS N_CURRENT_NUMBER,
	                        (T1.LAST_NUMBER +1)  AS N_NEXT_NUMBER,
	                        1 AS N_IDENTITY_VALUE
                        FROM ALL_SEQUENCES T1
                        WHERE
	                        T1.SEQUENCE_OWNER = 'THKQS') t WHERE 1=1");
            if (!string.IsNullOrEmpty(whereStr.Trim()))
            {
                if (whereStr.IndexOf("_") != -1)
                {
                    //将通配符_转换为字符串
                    string escCape = whereStr.Replace("_", @"\_");
                    strSql.AppendFormat(@" AND  t.c_col_name like '%{0}%' escape '\' OR t.c_table_name like '%{1}%'  escape '\' ", escCape, escCape);
                }
                else
                {
                    strSql.AppendFormat(@" AND t.c_col_name like '%{0}%' OR t.c_table_name like '%{1}%'", whereStr, whereStr);
                }
            }
            return GetCount(strSql.ToString());
        }

        public DataTable GetListByPage(string whereStr, string orderby, int startIndex, int endIndex)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(@"SELECT * FROM (SELECT  ROW_NUMBER() OVER(ORDER BY t." + orderby + @") as r,
    t.c_table_name,
    t.c_col_name,
    t.n_start_number,
    t.n_current_number,
    t.n_next_number,
    t.N_Identity_Value
FROM
    (SELECT
	                        T1.SEQUENCE_NAME AS C_TABLE_NAME,
	                        '' AS C_COL_NAME,
	                        T1.MIN_VALUE AS N_START_NUMBER,
	                        T1.LAST_NUMBER AS N_CURRENT_NUMBER,
	                        (T1.LAST_NUMBER +1)  AS N_NEXT_NUMBER,
	                        1 AS N_IDENTITY_VALUE
                        FROM ALL_SEQUENCES T1
                        WHERE
	                        T1.SEQUENCE_OWNER = 'THKQS') t
WHERE 1=1 ");
            if (!string.IsNullOrEmpty(whereStr.Trim()))
            {
                if (whereStr.IndexOf("_") != -1)
                {
                    //将通配符_转换为字符串
                    string escCape = whereStr.Replace("_", @"\_");
                    strSql.AppendFormat(@" AND  t.c_col_name like '%{0}%' escape '\' OR t.c_table_name like '%{1}%'  escape '\' ", escCape, escCape);
                }
                else
                {
                    strSql.AppendFormat(@" AND t.c_col_name like '%{0}%' OR t.c_table_name like '%{1}%'", whereStr, whereStr);
                }
            }
            strSql.Append(@" ) TT");
            if (startIndex >= 2)
            {
                strSql.AppendFormat(" WHERE TT.r BETWEEN ({0}-1)*{1}+1 AND {0}*{1}", startIndex, endIndex);
            }
            else
            {
                strSql.AppendFormat(" WHERE TT.r BETWEEN ({0}-1)*{1} AND {0}*{1}", startIndex, endIndex);
            }
            return Query(strSql.ToString());
        }

        public List<Qs_Sequences> ConvertToEntity(DataTable dt)
        {
            var data = new List<Qs_Sequences>();
            foreach (DataRow item in dt.Rows)
            {
                var entity = new Qs_Sequences
                {
                    CurC_Table_Name = Convert.ToString(item["C_TABLE_NAME"]),
                    CurC_Col_Name = Convert.ToString(item["C_COL_NAME"]),
                    CurN_Start_Number = Convert.ToDecimal(item["N_START_NUMBER"]),
                    CurN_Current_Number = Convert.ToDecimal(item["N_CURRENT_NUMBER"]),
                    CurN_Next_Number = Convert.ToDecimal(item["N_NEXT_NUMBER"]),
                    N_Identity_Value = Convert.ToDecimal(item["N_IDENTITY_VALUE"])
                };
                data.Add(entity);
            }
            return data;
        }
    }
}